package com.example.database_manage.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/*
   该界面主要用于建表，以及设定完整性
 */
public class DBOpenHelper extends SQLiteOpenHelper {

    //带全部参数的构造函数，此构造函数必不可少
    public DBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        // 创建学生信息表语句
        String sql_student_db = "create table student(" +
                "id int," +
                "sname varchar(20) not null," +
                "age int check(age<100 and age>0)," +
                "classroom varchar(20) ," +
                "sex varchar(10) CHECK(sex='男' OR sex='女') DEFAULT ('男')," +
                "birth datetime,\n" +
                "clubInfo varchar(20) DEFAULT('无')," +
                "awards varchar(100) DEFAULT('无')," +
                "graduationWork VARCHAR(50) DEFAULT('在校中')) ";
        //学生密码表
        String sql_student_pw = "create table load_account(account int default 1,password varchar(20))";
//        String sql_student_pw = "create table load_account(account int,password varchar(20))";


        //触发器
        String sql_ctrigger = "CREATE TRIGGER load_add AFTER INSERT ON student  " +
                "BEGIN INSERT INTO load_account(account,password) VALUES (new.id,'1'); END;";

        db.execSQL(sql_student_db);
        db.execSQL(sql_student_pw);
        db.execSQL(sql_ctrigger);


        String sql_insert_db1 = "\n" +
                "insert into student (id,sname,age,classroom,sex,birth)values (1234, '易政', 23,'计算机科学与技术Z-1班','男','1999-07-12')";
        db.execSQL(sql_insert_db1);
        String sql_insert_pw1 = "insert into load_account values(1234, '1')";
        db.execSQL(sql_insert_pw1);


        //老师个人信息表
        String sql_teacher = "create table teacher(" +
                "teacher_id int," +
                "name varchar(20)," +
                "sex  varchar(20) check(sex = '男' or sex = '女')," +
                "classroom varchar(20))";
        //老师登录信息表
        String sql_teacher_load = "create table load_teacher(" +
                "account int," +
                "password varchar(20))";
//        String sql_s_2 = "insert into load_teacher values(0987,'1')";
//        db.execSQL(sql_s_2);
        // 触发器
        String sql_ttrigger = "CREATE TRIGGER load_add_teacher AFTER INSERT ON teacher  " +
                "BEGIN INSERT INTO load_teacher(account,password) VALUES (new.teacher_id,'1'); END;";

        db.execSQL(sql_teacher_load);
        db.execSQL(sql_teacher);
        db.execSQL(sql_ttrigger);
//        db.execSQL(sql_dttrigger);
//        db.execSQL(sql_dttrigger1);

        String sql_insert_pw2 = "insert into load_teacher values (2017, '1')";
        String sql_insert_db2 = "insert into teacher values (2017, '大司马','男','计算机')";
        db.execSQL(sql_insert_pw2);
        db.execSQL(sql_insert_db2);



        //留言信息存储表
        String sql_liuyan = "create table message(" +
                "student_id int primary key not null," +
                "message text)";
        db.execSQL(sql_liuyan);


        //管理员表
        String sql_admin = "create table administractor(" +
                " account varchar(20)," +
                " password varchar(20))";
        String sql_insert_admin = "insert into administractor values('yz','1')";

        db.execSQL(sql_admin);
        db.execSQL(sql_insert_admin);


        //个人资源配置表，比如更改图片之类的啦
        String personal_resource = "create table personal_resource(" +
                "id int," +
                "IMAGE blob)";

        db.execSQL(personal_resource);



    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

}
